﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace Mark_dal
{
    public class DBHelper
    {
        private const string DB_CONNECTION_STRING_yy = @"Data Source=WMADDW57KYBGHZZ\小小鱼;Initial Catalog=market_yy;Integrated Security=True";
        private const string DB_CONNECTION_STRING_school = @"Data Source=.\SQLEXPRESS;Initial Catalog=mmsdb;Integrated Security=True";
        private SqlConnection conn = new SqlConnection(DB_CONNECTION_STRING_yy);

        /// <summary>
        /// 打开数据库
        /// </summary>
        public void Open()
        {
            conn.Open();
        
        }

        /// <summary>
        /// 关闭数据库
        /// </summary>
        public void Close()
        {
            conn.Close();
        }

        /// <summary>
        /// 非查询语句
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>受影响行数</returns>
        public int ExecuteNoQuery(string sql)
        {
            Open();
            return new SqlCommand(sql,conn).ExecuteNonQuery();
        }

        /// <summary>
        /// 只查询首行数据
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>查到的内容</returns>
     
        public object GetOne(string sql)
        {
            Open();
            return new SqlCommand(sql, conn).ExecuteScalar();
        }
        /// <summary>
        /// 查询所有内容
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public SqlDataReader GetAll(string sql)
        {
            Open();
            return new SqlCommand(sql, conn).ExecuteReader();
        }

        /// <summary>
        /// 查询内容，并返回一个表
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>数据表</returns>
        public DataTable GetTable(string sql)
        {
            DataTable dt = new DataTable();//使用SqlDataAdapter查询并填充数据
            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            da.Fill(dt);//填充数据
            return dt;
        }

    }
}
